<?php
/*
 * @FileForDetails: 课程控制器
 * @Date: 2022-11-25 18:35:28
 */
namespace app\crm\controller\product;

use app\common\controller\Backend;
use app\crm\model\product\Campus;
use app\crm\model\product\Course as ProductCourse;
use app\crm\model\product\Major;
use app\crm\validate\product\Course as ValidateProductCourse;
use think\Db;
use think\Exception;
use think\exception\PDOException;
use think\exception\ValidateException;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Course extends Backend
{
    protected $model = null;
    public function initialize() {
        $this->model = new ProductCourse();
        $this->assign('campusList', Campus::where('status',1)->all());// 校区
        $this->assign('majorList', Major::where('status',1)->all());  // 专业
    }

    /**
     * @Descripttion: 增加
     * @Date: 2022-11-29 10:29:44
     */
    public function addData()
    {
        // 1.验证请求方式
        if ($this->request->isAjax()) {
            // 2.获取请求数据
            $params = $this->request->post();
            
            // 3.验证数据
            $validata = new ValidateProductCourse();
            if (!$validata->check($params)) 
            return json(['code' => 0, 'msg' => $this->error($validata->getError())]);

            $result = false;
            Db::startTrans();
            try {
                // 4.数据库操作
                foreach(ProductCourse::all() as $val) {
                    if ($val['name'] == $params['name']) return json(['code' => 0, 'msg' => '课程名称已存在']);
                }
                $shopsModel = new ProductCourse();
                $result = $shopsModel->allowField(true)->save($params);
                Db::commit();
            } catch (ValidateException|PDOException|Exception $e) {
                Db::rollback();
                $this->error($e->getMessage());
            }

            return $result ? json(['code' => 1, 'msg' => '增加成功']) : json(['code' => 0, 'msg' => '增加失败']);
        }
    }

    /**
     * @Descripttion: 修改
     * @Date: 2022-11-29 10:29:44
     */  
    public function updateData()
    {
        // 1.验证请求方式
        if ($this->request->isAjax()) {
            // 2.获取请求数据
            $params = $this->request->post();

            // 3.验证数据
            $validata = new ValidateProductCourse();
            if (!$validata->check($params)) 
            return json(['code' => 0, 'msg' => $this->error($validata->getError())]);

            $result = false;
            Db::startTrans();
            try {
                // 4.数据库操作
                $shopsModel = new ProductCourse();
                $result = $shopsModel->allowField(true)->save($params, ['id' => $params['id']]);
                Db::commit();
            } catch (ValidateException|PDOException|Exception $e) {
                Db::rollback();
                $this->error($e->getMessage());
            }

            return $result ? json(['code' => 1, 'msg' => '修改成功']) : json(['code' => 0, 'msg' => '修改失败']);
        }
    }

    /**
     * @Descripttion: 获取导入模板
     * @Date: 2022-11-29 10:29:44
     */
    public function  download()
    {
        $title = ['课程名称', '价格', '课时/分钟', '校区id', '专业id'];

        $spreadsheet = new Spreadsheet;
        $sheet = $spreadsheet->getActiveSheet();

        //设置单元格内容
        $titCol = 'A';
        foreach ($title as $key => $value) {
            //单元格内容写入
            $sheet->setCellValue($titCol . '1', $value);
            $titCol++;
        }
        //保存
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $fileName = 'course.xlsx';
        //输出到浏览器
        header('Content-type: text/html; charset=utf-8');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename=' . $fileName);
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
    }

    /**
     * @Descripttion: 上传文件
     * @Date: 2022-11-29 10:29:44
     */
    public function uploading()
    {
        if ($this->request->isAjax()) {
            // 获取表单上传文件 例如上传了001.xlsx          
            $file = request()->file('file');
            
            //判断 是否上传了文件
            if (empty($file)) return json(['code' => 0, 'msg' => 'No results were found']);

            // 移动到框架应用根目录/uploads/ 目录下
            $info = $file->validate(['ext' => 'xlsx,xls,csv'])->move(ROOT_PATH . DS . 'public' . DS . 'uploads');
            if ($info) {
                // $path = 'D:\crm\public\uploads\/' . $info->getSaveName();
                $path = request()->domain() . DS . 'uploads' . DS . $info->getSaveName();
                return json(['code' => 1, 'msg' => '上传成功', 'data' => ['file' => $path]]);
            } else {
                // 上传失败获取错误信息
                return json(['code' => 0, 'msg' => $file->getError()]);
            }
        }
    }

    /**
     * @Descripttion: 录入数据库
     * @Date: 2022-11-29 10:29:44
     */
    public function uploadingSql()
    {
        $reader = IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load($_POST['title']); //载入excel表格
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数
        $highestColumn = $worksheet->getHighestColumn(); // 总列数
        $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
        $lines = $highestRow;
        if ($lines <= 1) {
            return json(['code' => 0, 'msg' => 'xlsx表格中没有数据']);
        }
        // '课程名称', '价格', '课时/分钟', '校区id', '专业id', '创建时间'
        $sql = "INSERT INTO `yd_crm_course` (`name`, `price`, `minute_hour`, `campus_id`,`major_id`) VALUES ";

        for ($row = 2; $row <= $highestRow; $row++) {
            $name        = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); 
            $price       = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); 
            $minute_hour = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); 
            $campus_id   = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); 
            $major_id    = $worksheet->getCellByColumnAndRow(5, $row)->getValue(); 
            $sql .= "('$name','$price','$minute_hour','$campus_id','$major_id'),";
        }
        $sql = rtrim($sql, ","); //去掉最后一个,号
        
        return ProductCourse::query($sql) > 0 ?  json(['code' => 1, 'msg' => '成功上传']) : json(['code' => 0, 'msg' => '上传失败']);
    }

    /**
     * @Descripttion: 渲染数据表格
     * @Date: 2022-11-25 18:50:14
     */    
    public function index()
    {
        if ($this->request->isAjax()) {
            $data = $this->request->get();
            // dump($data);
            if (count($data) >= 3) {
                // ->with('photo,basic') 多张表关联用这个   alias('a')给表起别名  join() 多表内链接
                $appoint = ProductCourse::alias('a')
                ->join('yd_crm_major m', 'a.major_id = m.id')
                ->join('yd_crm_campus c', 'a.campus_id = c.id')
                ->with('major,campus')
                ->WhereLike('a.name', '%' . $data['name'] . '%')
                ->WhereLike('m.name', '%' . $data['major_id'] . '%')
                ->WhereLike('c.name', '%' . $data['campus_id'] . '%')
                ->all();
                return json(['code' => 0, 'msg' => 'success', 'count' => count($appoint), 'data' => $appoint]);
            } else {
                $curr  = $_GET['page'] ?? 1;
                $limit = $_GET['limit'] ?? 5;
                $start = ($curr - 1) * $limit;
                $list  = ProductCourse::with('major,campus')->limit($start, $limit)->order('id','desc')->select();
                foreach ($list as $value) {
                    $value['unit_name'] = $value['unit_name'];  // 转换单位
                    $value['minute_hour'] = $value['minute_hour'] . '分钟';
                }
                return json(['code' => 0, 'msg' => 'success', 'count' => ProductCourse::count(), 'data' => $list]);
            }
        }
        return $this->fetch();
    }
}